﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.SqlClient;
using System.Data;
using System.Text;
using IDAL;
using Models;

namespace DAL
{
    public class ServiceInfoDAL:IServiceInfo
    {
        public DataBase DB = new DataBase();

        public int GetPageCount(int pagesize,int CategoryID)
        {
            DB.DatabaseConnection();
            DB.SQLCommand = DB.DatabaseCon.CreateCommand();
            DB.SQLCommand.CommandType = CommandType.StoredProcedure;
            DB.SQLCommand.CommandText = "UP_SERVICE_PAGECOUNT";
            DB.SQLCommand.Parameters.AddWithValue("@pagesize", pagesize);
            DB.SQLCommand.Parameters.AddWithValue("@categoryid", CategoryID);
            DB.SQLCommand.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
            DB.SQLCommand.ExecuteNonQuery();
            int n = (int)DB.SQLCommand.Parameters["@RETURN_VALUE"].Value;
            DB.CloseDB();
            return n;
        }

        public List<ServiceInfo> GetCategoryServiceInfo(int CategoryID, int pagesize, int nowpage)
        {
            List<ServiceInfo> list = new List<ServiceInfo>();
            DB.DatabaseConnection();
            SqlCommand cmd = DB.DatabaseCon.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "UP_SERVICE_PAGESELECT";
            cmd.Parameters.AddWithValue("@pagesize", pagesize);
            cmd.Parameters.AddWithValue("@categoryid", CategoryID);
            cmd.Parameters.AddWithValue("@nowpage", nowpage);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                ServiceInfo serviceInfo = new ServiceInfo();
                ReadtoServiceInfo(reader, serviceInfo);
                list.Add(serviceInfo);
            }
            DB.CloseDB();
            return list;
        }

        public void ReadtoServiceInfo(SqlDataReader reader, ServiceInfo serviceInfo)
        {
            serviceInfo.ServicePicURL =reader["ServicePicURL"] is DBNull?"":reader["ServicePicURL"].ToString();
            serviceInfo.ServiceURL =reader["ServiceURL"] is DBNull?"":reader["ServiceURL"].ToString();
            serviceInfo.ServiceName =reader["ServiceName"] is DBNull?"":reader["ServiceName"].ToString();
            serviceInfo.ServiceID = reader["ServiceID"] is DBNull ? 0 : Int32.Parse(reader["ServiceID"].ToString());
            serviceInfo.CategoryID = reader["CategoryID"] is DBNull ? 0 : Int32.Parse(reader["CategoryID"].ToString());
            serviceInfo.ServiceLevel = reader["ServiceLevel"] is DBNull ? 0 : Int32.Parse(reader["ServiceLevel"].ToString());
        }

        public int AddServiceInfo(ServiceInfo ser){
            int isSuccess = -1;
            string sql = @"INSERT INTO ServiceInfo ([CategoryID],[ServiceName],[ServicePicURL],[ServiceURL],[ServiceLevel],[ServiceTime])
                                   VALUES(" + ser.CategoryID.ToString() + @",
                                                '" + ser.ServiceName + @"',
                                                '" + ser.ServicePicURL + @"',
                                                '" + ser.ServiceURL + @"',
                                                " + ser.ServiceLevel.ToString() + @",
                                                '" + ser.ServiceTime.ToString() + "')";
            isSuccess = DB.UpdateRecords(sql);
            return isSuccess;
        }
        
        public ServiceInfo GetOneServiceInfo(int ServiceID)
        {
            ServiceInfo ser = new ServiceInfo();
            string sql = "SELECT * FROM ServiceInfo WHERE ServiceID = " + ServiceID;
            DB.DatabaseConnection();
            SqlDataReader reader = DB.GetdataRecords(sql);
            if (reader.HasRows && reader.Read())
            {
                ReadtoServiceInfo(reader, ser);
            }
            DB.CloseDB();
            return ser;
        }

        public int UpdateServiceInfo(ServiceInfo ser)
        {
            int isSuccess = -1;
            string sql = @"UPDATE ServiceInfo SET
                                [CategoryID] = " + ser.CategoryID + @",
                                [ServiceName] = '" + ser.ServiceName + @"',
                                [ServicePicURL] = '" + ser.ServicePicURL + @"',
                                [ServiceURL] = '" + ser.ServiceURL + @"',
                                [ServiceLevel] = " + ser.ServiceLevel + @",
                                [ServiceTime] = '" + ser.ServiceTime.ToString() + @"'
                                WHERE ServiceID = " + ser.ServiceID;
            isSuccess = DB.UpdateRecords(sql);
            return isSuccess;
        }

        public int DeleteServiceInfo(int sid)
        {
            int isSussess = -1;
            string sql = "DELETE FROM ServiceInfo WHERE ServiceID = " + sid;
            isSussess = DB.UpdateRecords(sql);
            return isSussess;
        }
    }
}
